﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

using System.Configuration;
using System.Collections;
using TuanHuy.DAL.DatabaseSpecific;

namespace TuanHuy.BLL
{
    public class ManagerBase
    {
        public static string connectionString
        {
            get;
            set;
        }
        public ManagerBase()
        {
            DataAccessAdapter adapter = new DataAccessAdapter();
            connectionString = adapter.ConnectionString;
        }

        public virtual string TableName()
        {
            return string.Empty;
        }

        protected void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, Hashtable cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (DictionaryEntry en in cmdParms)
                {
                    SqlParameter para = new SqlParameter("@" + en.Key, en.Value);
                    cmd.Parameters.Add(para);
                }
            }
        }

        #region Public Method
        protected int ExecuteNonQuery(CommandType cmdType, string cmdText)
        {
            return ExecuteNonQuery(cmdType, cmdText, null);
        }

        protected int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            int val = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();

                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                conn.Close();
            }
            return val;
        }

        protected int ExecuteNonQuery(CommandType cmdType, string cmdText, int TimeOut, params SqlParameter[] commandParameters)
        {
            int val = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();

                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                cmd.CommandTimeout = TimeOut;
                val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                conn.Close();
            }
            return val;
        }

        //Thực hiện việc lấy dữ liệu trong CSDL
        protected DataTable ExecuteDataTable(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {

                SqlCommand cmd = new SqlCommand();

                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                da.Dispose();
                cmd.Parameters.Clear();
                conn.Close();
            }

            return dt;
        }
        protected DataTable ExecuteDataTable(CommandType cmdType, string cmdText, int timeOut, params SqlParameter[] commandParameters)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandTimeout = timeOut;
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                da.Dispose();
                cmd.Parameters.Clear();
                conn.Close();
            }
            return dt;
        }
        protected DataTable ExecuteDataTable(CommandType cmdType, string cmdText)
        {
            return ExecuteDataTable(cmdType, cmdText, null);
        }

        protected DataTable ExecuteDataTable(Hashtable commandParameters, CommandType cmdType, string cmdText)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                da.Dispose();
                cmd.Parameters.Clear();
                conn.Close();
            }
            return dt;
        }
        protected DataTable ExecuteDataTable(Hashtable commandParameters, CommandType cmdType, string cmdText, int timeOut)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandTimeout = timeOut;
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                da.Dispose();
                cmd.Parameters.Clear();
                conn.Close();
            }
            return dt;
        }

        public static bool CheckConnection()
        {
            return CheckConnection(connectionString);
        }

        public static bool CheckConnection(string constring)
        {
            try
            {
                string strSQL = "Select 1";
                using (SqlConnection conn = new SqlConnection(constring))
                {
                    SqlCommand cmd = new SqlCommand();
                    DataTable dt = new DataTable();
                    if (conn.State != ConnectionState.Open)
                        conn.Open();

                    cmd.Connection = conn;
                    cmd.CommandText = strSQL;

                    cmd.CommandType = CommandType.Text;
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                    da.Dispose();
                    cmd.Parameters.Clear();
                    conn.Close();
                }
                return true;
            }
            catch
            {
                return false;
            }
        }
        #endregion
    }
}
